package controller.student;

import com.sun.deploy.util.FXLoader;
import controller.Items.coursegrade;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import sql.JDBC_T;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;

public class StudentGradeController {
    @FXML
    private TableView stu_myscore_tblList_score;
    public void update(String username) throws SQLException {
        JDBC_T jdbc_t = new JDBC_T();
        if(jdbc_t.is_conn()) {
            String sql = MessageFormat.format("\n" +
                    "SELECT xuanke.CID,course.Cname,xuanke.Cgrade\n" +
                    "FROM\n" +
                    "course,\n" +
                    "(SELECT cscore.CID,cscore.Cgrade\n" +
                    "FROM\n" +
                    "stuinfo,cscore\n" +
                    "WHERE\n" +
                    "stuinfo.Sname = \"{0}\" AND stuinfo.SID = cscore.SID) as xuanke\n" +
                    "WHERE xuanke.CID = course.CID",username);
            PreparedStatement preparedStatement = jdbc_t.getConn().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ObservableList<coursegrade> coursegrades = FXCollections.observableArrayList();
            String cid,cname,cgrade;
            while (resultSet.next()){
                cid =resultSet.getString("Cid");
                cname =resultSet.getString("Cname");
                cgrade =resultSet.getString("Cgrade");
                coursegrades.add(new coursegrade(cid,cname,cgrade));
            }
            ObservableList cols = stu_myscore_tblList_score.getColumns();
            TableColumn id = (TableColumn) cols.get(0);
            TableColumn name = (TableColumn) cols.get(1);
            TableColumn grade = (TableColumn) cols.get(2);
            id.setCellValueFactory(new PropertyValueFactory<>("CID"));
            name.setCellValueFactory(new PropertyValueFactory<>("CNAME"));
            grade.setCellValueFactory(new PropertyValueFactory<>("CGRADE"));
            stu_myscore_tblList_score.setItems(coursegrades);
        }
    }
}
